//@Time:2022/4/2 16:05
//@Author:aFun

package indi.csms_springboot.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import indi.csms_springboot.domain.*;
import org.apache.ibatis.annotations.*;


@Mapper
public interface OfficerMapper extends BaseMapper<Officer> {

    @Update("update course set judge=${judge},isopen=${isopen} where couId=${couId}")
    public boolean updateCourseJudge(@Param("judge")Integer judge,@Param("isopen")Integer isopen,@Param("couId")Integer couId);

    @Select("SELECT c.couName,COUNT(ss.stuId) as countSelected,MAX(ss.score) as maxScore,MIN(ss.score) as minScore,AVG(ss.score) as avgScore,COUNT(DISTINCT CASE WHEN ss.score>=60 THEN ss.ssid END) as passNum " +
            "FROM course c JOIN stu2score ss ON c.couId=ss.couId " +
            "${ew.customSqlSegment} " +
            "GROUP BY c.couId")
    public IPage<CountResultVO> countResult(Page<CountResultVO> studentScoreVOPage,
                                            @Param(Constants.WRAPPER)QueryWrapper<CountResultVO> wrapper);

    @Select(
            "SELECT ss.stuId,c.couId,u.name,c.couName " +
            "FROM USER u JOIN course c JOIN stu2score ss JOIN student s ON ss.couId=c.couId AND ss.stuId=s.stuId AND s.account=u.account " +
            "${ew.customSqlSegment}"
    )
    public IPage<StudentSelectCourseVO> selectionResult(Page<SSFOVO> studentScoreVOPage,
                                                        @Param(Constants.WRAPPER)QueryWrapper<SSFOVO> wrapper);

    @Delete(
            "delete from stu2score where stuId=${stuId} and couId=${couId}"
    )
    public boolean deleteStu2Score(@Param("stuId")Integer stuId,@Param("couId") Integer couId);


    @Select(
            "SELECT s.stuId,u.name,c.couId,c.couName,ss.score " +
            "FROM USER u JOIN course c JOIN student s JOIN stu2score ss ON u.account=s.account AND c.couId=ss.couId AND s.stuId=ss.stuId " +
            "${ew.customSqlSegment}"
    )
    public IPage<SSFOVO> studentScore(Page<SSFOVO> studentScoreVOPage,
                                      @Param(Constants.WRAPPER)QueryWrapper<SSFOVO> wrapper);



}
